package com.ruoyi.web.service;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.apache.poi.ss.usermodel.DataFormatter;

@Service
public class C2ResourceDetailUploadService {

    @Autowired
    private DruidDataSource druidDataSource;

    /**
     * 向 cnid_source_detail 新增资源统计数据
     *
     * @return
     */
    public void uploadCnidResource(MultipartFile file) throws Exception {
        try (InputStream inputStream = file.getInputStream();
             XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) {

            // 获取第一个 Sheet
            Sheet sheet = workbook.getSheetAt(0);

            // 建立数据库连接
            try (Connection connection = druidDataSource.getConnection()) {
                String sql = "INSERT INTO cnid_source_detail (id, processname, max90, max95, max97, version, date) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?)";
                try (PreparedStatement statement = connection.prepareStatement(sql)) {
                    for (Row row : sheet) {
                        if (row.getRowNum() == 0) continue; // 跳过表头

                        Long id = (long) row.getCell(0).getNumericCellValue();
                        String processName = row.getCell(1).getStringCellValue();
                        DataFormatter dataFormatter = new DataFormatter();
                        String version = dataFormatter.formatCellValue(row.getCell(2)); // 自动格式化为字符串
                        Double max90 = row.getCell(3).getNumericCellValue();
                        Double max95 = row.getCell(4).getNumericCellValue();
                        Double max97 = row.getCell(5).getNumericCellValue();
                        String dateRaw = dataFormatter.formatCellValue(row.getCell(6)); // 自动格式化为字符串

                        // 将日期从 YYYYMMDD 转换为 YYYY-MM-DD
                        String date = dateRaw.substring(0, 4) + "-" + dateRaw.substring(4, 6) + "-" + dateRaw.substring(6);

                        // 设置参数
                        statement.setLong(1, id);
                        statement.setString(2, processName);
                        statement.setDouble(3, max90);
                        statement.setDouble(4, max95);
                        statement.setDouble(5, max97);
                        statement.setString(6, version);
                        statement.setString(7, date);

                        // 执行 SQL 插入
                        statement.executeUpdate();
                    }
                }
            }
        }
    }

    /**
     * 向 apa_source_detail 新增资源统计数据
     *
     * @return
     */
    public void uploadApaResource(MultipartFile file) throws Exception {
        try (InputStream inputStream = file.getInputStream();
             XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) {

            // 获取第一个 Sheet
            Sheet sheet = workbook.getSheetAt(0);

            // 建立数据库连接
            try (Connection connection = druidDataSource.getConnection()) {
                String sql = "INSERT INTO apa_source_detail (id, processname, max90, max95, max97, version, date) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?)";
                try (PreparedStatement statement = connection.prepareStatement(sql)) {
                    for (Row row : sheet) {
                        if (row.getRowNum() == 0) continue; // 跳过表头

                        Long id = (long) row.getCell(0).getNumericCellValue();
                        String processName = row.getCell(1).getStringCellValue();
                        DataFormatter dataFormatter = new DataFormatter();
                        String version = dataFormatter.formatCellValue(row.getCell(2)); // 自动格式化为字符串
                        Double max90 = row.getCell(3).getNumericCellValue();
                        Double max95 = row.getCell(4).getNumericCellValue();
                        Double max97 = row.getCell(5).getNumericCellValue();
                        String dateRaw = dataFormatter.formatCellValue(row.getCell(6)); // 自动格式化为字符串

                        // 将日期从 YYYYMMDD 转换为 YYYY-MM-DD
                        String date = dateRaw.substring(0, 4) + "-" + dateRaw.substring(4, 6) + "-" + dateRaw.substring(6);

                        // 设置参数
                        statement.setLong(1, id);
                        statement.setString(2, processName);
                        statement.setDouble(3, max90);
                        statement.setDouble(4, max95);
                        statement.setDouble(5, max97);
                        statement.setString(6, version);
                        statement.setString(7, date);

                        // 执行 SQL 插入
                        statement.executeUpdate();
                    }
                }
            }
        }
    }
}
